﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
using System.Data.Odbc;

namespace Gift.Utile
{
    public class OperateExcel
    {

        public static void ExportCSVWithTitle(DataTable dt, string sFileName,string sTitle)
        {
            HttpContext.Current.Response.Clear();
            System.IO.StringWriter sw = new System.IO.StringWriter();
            int iColCount = dt.Columns.Count;
            if (!string.IsNullOrEmpty(sTitle))
            {
                sw.Write("\"" + sTitle + "\"");
                sw.Write(sw.NewLine);
            }
          
            for (int i = 0; i < iColCount; i++)
            {
                sw.Write("\"" + dt.Columns[i] + "\"");
                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                        sw.Write("\"" + dr[i].ToString() + "\"");
                    else
                        sw.Write("\"\"");
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + sFileName);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            HttpContext.Current.Response.Write(sw);
            HttpContext.Current.Response.End();
        }


         public static void ExportCSV(DataTable dt,string sFileName)
        {
            HttpContext.Current.Response.Clear();
            System.IO.StringWriter sw = new System.IO.StringWriter();
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                sw.Write("\"" + dt.Columns[i] + "\"");
                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                        sw.Write("\"" + dr[i].ToString() + "\"");
                    else
                        sw.Write("\"\"");
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + sFileName);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            HttpContext.Current.Response.Write(sw);
            HttpContext.Current.Response.End();
        }

         public static DataTable InputCSV(string sFileName)
        {
          string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
          strConn += ";Extensions=asc,csv,tab,txt;";
          OdbcConnection objConn = new OdbcConnection(strConn);
          DataSet ds = new DataSet();
          try
          {
              string strSql = "select * from " + sFileName;
          OdbcDataAdapter odbcCSVDataAdapter = new OdbcDataAdapter(strSql, objConn);
          odbcCSVDataAdapter.Fill(ds);
          return ds.Tables[0];

          }
          catch (Exception ex)
          {
                 throw ex;
          }
         }


  


    }
}
